﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
using System.Data;
using System.IO;

namespace ParaEditor.Infrastructure
{
    public class DataBase
    {
        public OracleConnection connectionCCDB;
        static string connection;

        static DataBase()
        {
            connection = "Data Source=";
            connection = connection + Profile.getServerName();
            connection = connection + ";User Id=";
            connection = connection + Profile.getLogId();
            connection = connection + ";Password=";
            connection = connection + Profile.getPassWord();
            connection = connection + ";";

            //connectionCCDB = new OracleConnection(connection);
            //test
            //connection = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=11.40.90.104)(PORT=1521))(CONNECT_DATA=(SID=orcl)));User Id=scott;Password=test;";
        }

        public OracleConnection OpenConnection()
        {
            OracleConnection connectionCCDB = new OracleConnection(connection);
            if (connectionCCDB.State == ConnectionState.Closed)//判断连接是否关闭
            {
                try
                {
                    connectionCCDB.Open();
                }
                catch (Exception ex)
                {
                    throw new Exception("数据库出错，请联系管理员：" + ex.Message);
                }
            }
            return connectionCCDB;
        }

        public static bool IsOraCon()
        {
            bool flag = false;
            int value = 0;
            string sqlText = "select 1 from dual";
            OracleDataReader dataReader;
            try
            {
                dataReader = DataBase.SqlReader(sqlText);
                while (dataReader.Read())
                {
                    value = (int)dataReader.GetOracleNumber(0);
                }
                dataReader.Close();
                dataReader.Dispose();
                if (value == 1)
                {
                    flag = true;
                }
                return flag;
            }
            catch (Exception e)
            {
                return flag;
            }
        }

        /// <summary>
        /// 检查是否能连接上数据库
        /// </summary>
        public static void CheckConnection()
        {
            OracleConnection connectionCCDB = new OracleConnection(connection);
            try
            {
                connectionCCDB.Open();
            }
            catch (Exception ex)
            {
                throw new Exception("数据库出错，请联系管理员：" + ex.Message);
            }
            finally
            {
                if (connectionCCDB.State.ToString() != "Closed")
                {
                    connectionCCDB.Close();
                }
            }
        }

        public static OracleDataReader SqlReader(string sql)
        {
            DataBase dataBase = new DataBase();
            OracleConnection conn = dataBase.OpenConnection();

            OracleDataReader dataReader = null;
            OracleCommand sqlcmd = new OracleCommand(sql, conn);
            if (sqlcmd.Connection.State.ToString() == "Closed")
            {
                sqlcmd.Connection.Open();
            }
            try
            {
                dataReader = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception e)
            {
                throw;
            }
            return dataReader;
        }

        public static string SqlCommand(string queryString)
        {
            DataBase dataBase = new DataBase();
            OracleConnection conn = dataBase.OpenConnection();

            string errorstr = null;
            OracleCommand sqlcmd = new OracleCommand(queryString, conn);
            if (sqlcmd.Connection.State.ToString() == "Closed")
            {
                sqlcmd.Connection.Open();
            }
            try
            {
                sqlcmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw;
            }
            finally
            {
                if (conn.State.ToString() != "Closed")
                {
                    conn.Close();
                }
            }

            return errorstr;
        }
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="StoredProcedure"></param>
        /// <returns></returns>
        public static void SqlCommandStoredProcedure(string StoredProcedure)
        {
            DataBase dataBase = new DataBase();
            OracleConnection conn = dataBase.OpenConnection();

            OracleCommand orcmd = new OracleCommand();
            orcmd.Connection = conn;
            orcmd.CommandType = CommandType.StoredProcedure;
            orcmd.Parameters.Add("batch_day", SqlDbType.VarChar);
            orcmd.CommandText = StoredProcedure;

            orcmd.Parameters["batch_day"].Value = "20120207";
            if (orcmd.Connection.State.ToString() == "Closed")
            {
                orcmd.Connection.Open();
            }
            try
            {
                orcmd.ExecuteNonQuery();
                orcmd.CommandType = CommandType.Text;
            }
            catch (Exception e)
            {
                throw;
            }
            finally
            {
                if (conn.State.ToString() != "Closed")
                {
                    conn.Close();
                }
            }
        }
        /// <summary>
        /// 执行存储过程,带操作日期的参数
        /// </summary>
        /// <param name="StoredProcedure"></param>
        /// <returns></returns>
        public static void SqlCommandStoredProcedure(string StoredProcedure, string operatedate)
        {
            DataBase dataBase = new DataBase();
            OracleConnection conn = dataBase.OpenConnection();

            OracleCommand orcmd = new OracleCommand();
            orcmd.Connection = conn;
            orcmd.CommandType = CommandType.StoredProcedure;
            orcmd.Parameters.Add("batch_day", SqlDbType.VarChar);
            orcmd.CommandText = StoredProcedure;

            orcmd.Parameters["batch_day"].Value = operatedate;
            if (orcmd.Connection.State.ToString() == "Closed")
            {
                orcmd.Connection.Open();
            }
            try
            {
                orcmd.ExecuteNonQuery();
                orcmd.CommandType = CommandType.Text;
            }
            catch (Exception e)
            {
                throw;
            }
            finally
            {
                if (conn.State.ToString() != "Closed")
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 执行存储过程,带操作日期的参数--分时段客流统计，间隔2小时存储过程
        /// </summary>
        /// <param name="StoredProcedure"></param>
        /// <returns></returns>
        public static void SqlCommandStoredProcedure2Stream(string StoredProcedure, string operatedate)
        {
            DataBase dataBase = new DataBase();
            OracleConnection conn = dataBase.OpenConnection();

            OracleCommand orcmd = new OracleCommand();
            orcmd.Connection = conn;
            orcmd.CommandType = CommandType.StoredProcedure;
            orcmd.Parameters.Add("batch_date", SqlDbType.VarChar);
            orcmd.CommandText = StoredProcedure;

            orcmd.Parameters["batch_date"].Value = operatedate;
            if (orcmd.Connection.State.ToString() == "Closed")
            {
                orcmd.Connection.Open();
            }
            try
            {
                orcmd.ExecuteNonQuery();
                orcmd.CommandType = CommandType.Text;
            }
            catch (Exception e)
            {
                throw;
            }
            finally
            {
                if (conn.State.ToString() != "Closed")
                {
                    conn.Close();
                }
            }
        }


        /// <summary>
        /// 执行存储过程,带操作日期的参数--单程票回收
        /// </summary>
        /// <param name="StoredProcedure"></param>
        /// <returns></returns>
        public static void SqlCommandStoredProcedureDCP(string StoredProcedure, string operatedate)
        {
            DataBase dataBase = new DataBase();
            OracleConnection conn = dataBase.OpenConnection();

            OracleCommand orcmd = new OracleCommand();
            orcmd.Connection = conn;
            orcmd.CommandType = CommandType.StoredProcedure;
            orcmd.Parameters.Add("in_day", SqlDbType.VarChar);
            orcmd.CommandText = StoredProcedure;

            orcmd.Parameters["in_day"].Value = operatedate;
            if (orcmd.Connection.State.ToString() == "Closed")
            {
                orcmd.Connection.Open();
            }
            try
            {
                orcmd.ExecuteNonQuery();
                orcmd.CommandType = CommandType.Text;
            }
            catch (Exception e)
            {
                throw;
            }
            finally
            {
                if (conn.State.ToString() != "Closed")
                {
                    conn.Close();
                }
            }
        }

        public static DataSet sqlDataSet(string QueryString)
        {
            DataBase dataBase = new DataBase();
            OracleConnection conn = dataBase.OpenConnection();

            DataSet ds = new DataSet();
            ds.EnforceConstraints = false;
            OracleCommand sqlcmd = new OracleCommand();
            try
            {
                sqlcmd.Connection = conn;
                OracleDataAdapter dataAdapter = new OracleDataAdapter();
                sqlcmd.CommandText = QueryString;
                dataAdapter.SelectCommand = sqlcmd;
                dataAdapter.Fill(ds, "table");
            }
            catch (Exception e)
            {
                throw;
            }
            finally
            {
                if (conn.State.ToString() != "Closed")
                {
                    conn.Close();
                }
            }
            return ds;
        }

        /// <summary>
        /// 返回datatable
        /// </summary>
        /// <param name="QueryString"></param>
        /// <returns></returns>
        public static DataTable sqlDataTable(string QueryString)
        {
            DataBase dataBase = new DataBase();
            OracleConnection conn = dataBase.OpenConnection();

            DataTable WorkDataTable = new DataTable();
            OracleCommand sqlcmd = new OracleCommand();
            try
            {
                sqlcmd.Connection = conn;
                OracleDataAdapter dataAdapter = new OracleDataAdapter();
                sqlcmd.CommandText = QueryString;
                dataAdapter.SelectCommand = sqlcmd;
                dataAdapter.Fill(WorkDataTable);
            }
            catch (Exception e)
            {
                throw;
            }
            finally
            {
                if (conn.State.ToString() != "Closed")
                {
                    conn.Close();
                }
            }
            return WorkDataTable;
        }


        public static int GetCount(string QueryString)
        {
            DataBase dataBase = new DataBase();
            OracleConnection conn = dataBase.OpenConnection();

            OracleCommand sqlcmd = new OracleCommand();
            int iRet = 0;
            try
            {
                sqlcmd.Connection = conn;
                OracleDataAdapter dataAdapter = new OracleDataAdapter();
                sqlcmd.CommandText = QueryString;
                OracleDataReader dataReader = sqlcmd.ExecuteReader();
                while (dataReader.Read())
                {
                    iRet += 1;
                }
                dataReader.Close();
                dataReader.Dispose();
                sqlcmd.Dispose();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (conn.State.ToString() != "Closed")
                {
                    conn.Close();
                }
            }
            return iRet;

        }

        /* ----------------------------------------------------- */
        /*                       写日志文件                      */
        /* ----------------------------------------------------- */
        public static void WriteAppLog(StringBuilder buffer)
        {
            StringBuilder errmsg = new StringBuilder(500);
            errmsg.Append(GetCurTime());

            errmsg.Append(buffer);
            FileStream fs = new FileStream("E:\\164project\\CheckApplog\\infomessage" + DateTime.Now.ToString("yyyyMMddHHmmss"), FileMode.CreateNew);//

            StreamWriter bw = new StreamWriter(fs);
            bw.Write(buffer);
            bw.Flush();
            bw.Close();
        }

        /* ----------------------------------------------------- */
        /*                       获得本地时间                    */
        /* ----------------------------------------------------- */
        public static StringBuilder GetCurTime()
        {
            DateTime dt = DateTime.Now;
            StringBuilder localtime = new StringBuilder();
            localtime.Append(string.Format("{0:yyyy-MM-dd hh:mm:ss}", dt));
            return localtime;
        }
    } 
}
